package Jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.StringTokenizer;

public class JdbcGokhan {

	static Connection con = null;
	static Statement st = null;
	static ResultSet rs = null;
	static PreparedStatement pst = null;

    static public void Connect(){
    		
		    String url = "jdbc:postgresql://localhost:5432/yazgellab";
		    String user = "postgres";
		    String password = "12345";
		
		    try {
		    	Class.forName("org.postgresql.Driver");
		        con = DriverManager.getConnection(url, user, password);
		
		    } catch (Exception ex) {
		      System.out.println("baglanilamadi");
		
		    }
      }
    
    public static String makaleKaydet(int uye_id, ArrayList<String> konu, ArrayList<String> bilim_dali, String dergi, String etiket, String baslik, String editor, String makale_yolu){
    	Connect();
    	StringTokenizer token = new StringTokenizer(dergi, "*");
    	int dergi_pk = Integer.parseInt(token.nextToken());
    	int yazar_pk;
    	int max_makale_pk = 0;
    	int max_konu_makale_pk = 0;
    	int max_bilim_dali_makale_pk = 0;
    	int max_editor_depo_makale_pk = 0;
    	
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT MAX(makale_pk) FROM makale");
			if (rs.next()){
				max_makale_pk = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT MAX(konu_makale_pk) FROM konu_makale");
			if (rs.next()){
				max_konu_makale_pk = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT MAX(bilim_dali_makale_pk) FROM bilim_dali_makale");
			if (rs.next()){
				max_bilim_dali_makale_pk = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT MAX(editor_depo_makale_pk) FROM editor_depo_makale");
			if (rs.next()){
				max_editor_depo_makale_pk = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT yazar_pk FROM yazar WHERE uye_pk='" + uye_id + "'");
			if (rs.next()){
				yazar_pk = (rs.getInt(1));
					
				try {
				   	String stm = "INSERT INTO makale(yayinlanma_yili, version, makale_pk, yayinlanma_sirasi, history, baslik, makale_yolu, yayinlanma_sayisi, durum, yazar_pk, dergi_pk) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				   	pst = con.prepareStatement(stm);
				   	pst.setInt(1, 0);
				   	pst.setInt(2, 1);
				   	pst.setInt(3, max_makale_pk + 1);
				   	pst.setInt(4, 0);
				   	pst.setString(5, null);
				   	pst.setString(6, baslik);
				   	pst.setString(7, makale_yolu);
				   	pst.setInt(8, 0);
				   	pst.setInt(9, 0);
				   	pst.setInt(10, yazar_pk);
			    	pst.setInt(11, dergi_pk);
				   	pst.executeUpdate();		   	
			 	} catch (SQLException e) {
					// TODO Auto-generated catch block
			 		e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	//konu ekle
    	for (int i = 0; i < konu.size(); i++){
    		StringTokenizer token1 = new StringTokenizer(konu.get(i), "*");
    		int konu_pk = Integer.parseInt(token1.nextToken());
			try {
				String stm = "INSERT INTO konu_makale(konu_makale_pk, bilim_dali_pk, makale_pk) VALUES (?, ?, ?)";
				pst = con.prepareStatement(stm);
				pst.setInt(1, max_konu_makale_pk + 1);
				max_konu_makale_pk = max_konu_makale_pk + 1;
				pst.setInt(2, konu_pk);
				pst.setInt(3, max_makale_pk + 1);
				pst.executeUpdate();				    		
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    	
    	//bilim_dali ekle
    	for (int i = 0; i < bilim_dali.size(); i++){
    		StringTokenizer token1 = new StringTokenizer(bilim_dali.get(i), "*");
    		int bilim_dali_pk = Integer.parseInt(token1.nextToken());
    		try {
			    String stm = "INSERT INTO bilim_dali_makale(bilim_dali_makale_pk, bilim_dali_pk, makale_pk) VALUES (?, ?, ?)";
			    pst = con.prepareStatement(stm);
			    pst.setInt(1, max_bilim_dali_makale_pk + 1);
			    max_bilim_dali_makale_pk = max_bilim_dali_makale_pk + 1;
			    pst.setInt(2, bilim_dali_pk);
			    pst.setInt(3, max_makale_pk + 1);
			    pst.executeUpdate();
			    		
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    	
    	StringTokenizer token2 = new StringTokenizer(editor, "*");
    	int editor_pk = Integer.parseInt(token2.nextToken());
    	int editor_makale_deposu_pk;
    	try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT editor_makale_deposu_pk FROM editor_makale_deposu WHERE editor_pk = '" + editor_pk + "'");
			if (rs.next()){
				editor_makale_deposu_pk = (rs.getInt(1));
		    	try {
					st = con.createStatement();
					rs = st.executeQuery("SELECT editor_makale_deposu_pk FROM editor_makale_deposu WHERE editor_pk = '" + editor_pk + "'");
					if (rs.next()){
						editor_makale_deposu_pk = (rs.getInt(1));
						try {
							String stm = "INSERT INTO editor_depo_makale(editor_depo_makale_pk, editor_makale_deposu_pk, makale_pk) VALUES (?, ?, ?)";
							pst = con.prepareStatement(stm);
							pst.setInt(1, max_editor_depo_makale_pk + 1);
							pst.setInt(2, editor_makale_deposu_pk);
							pst.setInt(3, max_makale_pk + 1);
							pst.executeUpdate();				    		
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	finally{
    		close();
    	}
    	
    	return "SUCCESS";
    }
    
    public static String makaleListeleYazar(int uye_pk, int durum){
    	Connect();
    	String sorgu;
    	String dergi_isim = null;
    	Statement st2 = null;
    	ResultSet rs2 = null;
    	if (durum == 0){
    		sorgu = "SELECT baslik, makale.makale_pk, makale.dergi_pk, makale.makale_yolu FROM makale WHERE (durum = '0' OR durum = '1' OR durum = '2') AND makale_pk IN (SELECT makale_pk FROM yazar_depo_makale WHERE yazar_makale_deposu_pk = (SELECT yazar_makale_deposu_pk FROM yazar_makale_deposu WHERE yazar_pk = (SELECT yazar_pk FROM yazar WHERE uye_pk = '" + uye_pk + "'" + ")))";
    	}
    	else {
    		sorgu = "SELECT baslik, makale.makale_pk, makale.dergi_pk, makale.makale_yolu FROM makale WHERE durum = '" + durum + "' AND makale_pk IN (SELECT makale_pk FROM yazar_depo_makale WHERE yazar_makale_deposu_pk = (SELECT yazar_makale_deposu_pk FROM yazar_makale_deposu WHERE yazar_pk = (SELECT yazar_pk FROM yazar WHERE uye_pk = '" + uye_pk + "'" + ")))";
    	}
    	String makaleler = ",";
    	try {
			st = con.createStatement();
			rs = st.executeQuery(sorgu);
			while(rs.next()){
				try {
					st2 = con.createStatement();
					rs2 = st2.executeQuery("SELECT isim FROM dergi WHERE dergi_pk='" + rs.getString(3) + "'");
					if (rs2.next()){
						dergi_isim = rs2.getString(1);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				makaleler = makaleler + Integer.toString(rs.getInt(2)) + "*" + rs.getString(1) + "*" + dergi_isim + "*" + rs.getString(4) + ",";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	finally{
    		close();
    	}
    	return makaleler;
    }
    
    static public ArrayList<String> dergi_isim_bul(){
    	Connect();
		ArrayList<String> dergi = new ArrayList<String>();
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT * FROM dergi");
			while (rs.next()){
				dergi.add(rs.getInt(1) + "*" + rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return dergi;
    }
    
    static public ArrayList<String> konu_bul(){
    	Connect();
		ArrayList<String> konu = new ArrayList<String>();
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT * FROM konu");
			while (rs.next()){
				konu.add(rs.getInt(1) + "*" +rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return konu;
    }
    
    static public ArrayList<String> bilim_dali_bul(){
    	Connect();
		ArrayList<String> bilim_dali = new ArrayList<String>();
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT * FROM bilim_dali");
			while (rs.next()){
				bilim_dali.add(rs.getInt(1) + "*" + rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return bilim_dali;
    }
    
    public static String derginin_editor_bul(String dergi){
    	Connect();
		String editor = ",";
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT ad,editor.editor_pk FROM dergi_editor,editor,profil WHERE dergi_pk = '" + dergi + "' AND dergi_editor.editor_pk = editor.editor_pk AND profil.uye_pk = editor.uye_pk");
			while (rs.next()){
				editor = editor + Integer.toString(rs.getInt(2)) + "*" + rs.getString(1) + ",";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return editor;
    }
    
    public static String MakaleDuzelt(int makale_pk, String makale_yolu){
    	Connect();
    	int version = 0;
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT version FROM makale WHERE makale_pk='" + makale_pk + "'");
			if (rs.next()){
				version = rs.getInt(1) + 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			st = con.createStatement();
			st.executeUpdate("UPDATE makale SET version='" + version + "' , makale_yolu= '" + makale_yolu + "', durum = '0' WHERE makale_pk = '" + makale_pk + "'");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return "SUCCESS";
    }
    
    public static void close(){
    	
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }

        } catch (SQLException ex) {
            System.out.println("Kapatilamadi");
        }
    }
    
}
